from django.http import HttpResponse, JsonResponse
from django.views.decorators.http import require_http_methods
import base64, json
from .confs.conf import get_mirror_conn
from openpyxl import Workbook
from openpyxl.styles import numbers
from io import BytesIO
from datetime import datetime, timedelta

def_response_o = {
    "code": 0,
    "result": "",
    "message": "",
    "type": 'success',
}

counts = """SELECT COUNT(*) """
select = '''
SELECT tm,evname, count(tl) AS 'num',sum(money) as 'money' FROM
    ( SELECT uid, SUBSTRING(DATE_ADD(`time`, INTERVAL 8 HOUR), 1, 10) AS tm, COUNT(*) AS tl,
    sum(SUBSTRING_INDEX(SUBSTRING_INDEX(content,",uemail:",1),'total:',-1)) as money, evname FROM `gather_record`
    WHERE evname in ('ev_name') AND `time`>= 'star_time' AND `time`< 'end_time' AND `host` = 'site_str'
    AND sip not in (select ip from `dlz_exclude_ip`)
    GROUP BY tm, uid
) AS tb GROUP BY tm ORDER BY tm
'''

select_checkout = '''
SELECT tm,evname, count(tl) AS 'num',sum(money) as 'money' FROM
    ( SELECT uid, SUBSTRING(DATE_ADD(`time`, INTERVAL 8 HOUR), 1, 10) AS tm, COUNT(*) AS tl,
    sum(SUBSTRING_INDEX(SUBSTRING_INDEX(content,",uemail:",1),'total:',-1)) as money, evname FROM `gather_record`
    WHERE evname in ('ev_name') AND `time`>= 'star_time' AND `time`< 'end_time' AND `host` = 'site_str' AND url like 'like_str'
    AND sip not in (select ip from `dlz_exclude_ip`)
    GROUP BY tm, uid
) AS tb GROUP BY tm ORDER BY tm
'''

select_new_user = '''
SELECT tm,evname, count(tl) AS 'num',sum(money) as 'money' FROM
    ( SELECT uid, SUBSTRING(DATE_ADD(`time`, INTERVAL 8 HOUR), 1, 10) AS tm, COUNT(*) AS tl,
    sum(SUBSTRING_INDEX(SUBSTRING_INDEX(content,",uemail:",1),'total:',-1)) as money, evname FROM `gather_record`
    WHERE evname in ('ev_name') AND `time`>= 'star_time' AND `time`< 'end_time' AND `host` = 'site_str'
    AND sip not in (select ip from `dlz_exclude_ip`)
    AND uid not in (select uid from gather_dlzuserinfo where `create_time`>= 'fifteen_day_ago' 
                    AND `create_time`< 'one_day_ago' AND `site` = 'site_str')
    GROUP BY tm, uid
) AS tb GROUP BY tm ORDER BY tm
'''

select_zero_thanks = '''
SELECT tm,evname, count(tl) AS 'num',sum(money) as 'money' FROM
    ( SELECT uid, SUBSTRING(DATE_ADD(`time`, INTERVAL 8 HOUR), 1, 10) AS tm, COUNT(*) AS tl,
    sum(SUBSTRING_INDEX(SUBSTRING_INDEX(content,",uemail:",1),'total:',-1)) as money, evname FROM `gather_record`
    WHERE evname in ('ev_name') AND `time`>= 'star_time' AND `time`< 'end_time' AND `host` = 'site_str'
    AND sip not in (select ip from `dlz_exclude_ip`) and SUBSTRING_INDEX(SUBSTRING_INDEX(content,",uemail:",1),'total:',-1)=0
    GROUP BY tm, uid
) AS tb GROUP BY tm ORDER BY tm
'''

select_zero_create = '''
SELECT tm,evname, count(tl) AS 'num',sum(money) as 'money' FROM
    ( SELECT uid, SUBSTRING(DATE_ADD(`time`, INTERVAL 8 HOUR), 1, 10) AS tm, COUNT(*) AS tl,
    sum(SUBSTRING_INDEX(SUBSTRING_INDEX(content,",order_id:",1),'total:',-1)) as money, evname FROM `gather_record`
    WHERE evname in ('ev_name') AND `time`>= 'star_time' AND `time`< 'end_time' AND `host` = 'site_str'
    AND sip not in (select ip from `dlz_exclude_ip`) 
    and SUBSTRING_INDEX(SUBSTRING_INDEX(content,"order_id:",1),'total:',-1) !=""
    and SUBSTRING_INDEX(SUBSTRING_INDEX(content,",order_id:",1),'total:',-1)=0
    GROUP BY tm, uid
) AS tb GROUP BY tm ORDER BY tm
'''

select_new_data = '''
SELECT
    sd.id,
    sd.site,
    sd.datetime,
    sd.crate_time,
    sd.money,
    sd.money_per,
    sd.order_thanks,
    sd.checkout_create,
    sd.addto_cart,
    sd.checkout_out,
    sd.new_num,
    sd.load_page,
    sd.add_per,
    sd.cart2_c,
    sd.u2_checkout,
    sd.u2_o,
    sd.c2_o,
    sd.o2_p,
    sd.c2_p,
    sd.zero_order_thanks,
    sd.zero_checkout_create,
    sd.zero_per,
    sd.u2_p,
    sd.ep_u
FROM
    dlz_site_statistics_days AS sd
WHERE 
`site` = 'site_str' AND `crate_time`>= 'star_time' AND `crate_time`< 'end_time'
'''


def get_data(start: datetime, end: datetime, num_days: int, site: str, country: str):
    result = []
    types = ["load-page", "addto-cart", "checkout-create", "order-thanks"]
    for day in range(0, num_days):
        one_day_data = {}
        next_star = start + timedelta(days=day) - timedelta(hours=8)
        next_end = start + timedelta(days=day + 1) - timedelta(hours=8)
        for tp in types:
            db_conn = get_mirror_conn()
            sql = f'''{select.replace('star_time', str(next_star)).replace('end_time', str(next_end)).replace('ev_name', tp).replace('site_str', site)};'''
            sql = sql_link_country(sql, country, site,tp)
            print(sql)
            r = db_conn.query_all(sql)
            one_day_data[tp] = r

        # 查询结账次数
        tp2 = "checkout_out"
        db_conn_2 = get_mirror_conn()
        sql_2 = f'''
            {select_checkout.replace('star_time', str(next_star))
            .replace('end_time', str(next_end)).replace('ev_name', 'load-page')
            .replace('site_str', site)
            .replace("like_str", str('%%checkout%%'))};
                '''
        sql_2 = sql_link_country(sql_2, country, site,tp2)
        # print(sql_2)
        r2 = db_conn_2.query_all(sql_2)
        one_day_data[tp2] = r2

        # 查询新用户数
        fifteen_day_ago = start + timedelta(days=day - 15) - timedelta(hours=8)
        one_day_ago = start + timedelta(days=day - 1) - timedelta(hours=8)
        tp3 = "new_user"
        db_conn_3 = get_mirror_conn()
        sql_3 = f'''
            {select_new_user.replace('star_time', str(next_star))
            .replace('end_time', str(next_end)).replace('ev_name', 'load-page')
            .replace('site_str', site)
            .replace("like_str", str('%%checkout%%'))
            .replace('fifteen_day_ago', str(fifteen_day_ago)).replace('one_day_ago', str(one_day_ago))};
                '''
        sql_3 = sql_link_country(sql_3, country, site,tp3)
        print(sql_3)
        r3 = db_conn_3.query_all(sql_3)
        # print(r3)
        one_day_data[tp3] = r3

        # 查询0元购买
        tp4 = "zero_thanks"
        db_conn_4 = get_mirror_conn()
        sql_4 = f'''
              {select_zero_thanks.replace('star_time', str(next_star))
            .replace('end_time', str(next_end)).replace('ev_name', 'order-thanks')
            .replace('site_str', site)};
                  '''
        # print(sql_4)
        sql_4 = sql_link_country(sql_4, country, site,tp4)
        r4 = db_conn_4.query_all(sql_4)
        one_day_data[tp4] = r4

        # 查询0元 ORDER NUMBER
        tp5 = "zero_create"
        db_conn_5 = get_mirror_conn()
        sql_5 = f'''
              {select_zero_create.replace('star_time', str(next_star))
            .replace('end_time', str(next_end)).replace('ev_name', 'checkout-create')
            .replace('site_str', site)};
                  '''
        # print(sql_5)
        sql_5 = sql_link_country(sql_5, country, site,tp5)
        r5 = db_conn_5.query_all(sql_5)
        one_day_data[tp5] = r5

        # 处理数据
        one_result = {}
        money = round(one_day_data['order-thanks'][0]['money'],4) if len(one_day_data['order-thanks']) > 0 else 0
        load_page = int(one_day_data['load-page'][0]['num']) if len(one_day_data['load-page']) > 0 else 0
        new_user = int(one_day_data['new_user'][0]['num']) if len(one_day_data['new_user']) > 0 else 0
        addto_cart = int(one_day_data['addto-cart'][0]['num']) if len(one_day_data['addto-cart']) > 0 else 0
        checkout_create = int(one_day_data['checkout-create'][0]['num']) if len(
            one_day_data['checkout-create']) > 0 else 0
        checkout_out = int(one_day_data['checkout_out'][0]['num']) if len(one_day_data['checkout_out']) > 0 else 0
        order_thanks = int(one_day_data['order-thanks'][0]['num']) if len(one_day_data['order-thanks']) > 0 else 0
        zero_order_thanks = int(one_day_data['zero_thanks'][0]['num']) if len(one_day_data['zero_thanks']) > 0 else 0
        zero_checkout_create = int(one_day_data['zero_create'][0]['num']) if len(one_day_data['zero_create']) > 0 else 0

        add_per = round((addto_cart / load_page), 4) if load_page else 000
        money_per = round((money / (order_thanks - zero_order_thanks)), 4) if order_thanks else 000
        u2_checkout = round((checkout_out / load_page), 4) if load_page else 000
        cart2_c = round((checkout_out / addto_cart), 4) if addto_cart else 000
        u2_o = round(((checkout_create - zero_checkout_create) / load_page), 4) if load_page else 000
        c2_o = round(((checkout_create - zero_checkout_create) / checkout_out), 4) if checkout_out else 000
        print(checkout_create)
        print(zero_checkout_create)
        o2_p = round(((order_thanks - zero_order_thanks) / (checkout_create - zero_checkout_create)),
                     4) if checkout_create and (checkout_create - zero_checkout_create > 0) else 000
        c2_p = round(((order_thanks - zero_order_thanks) / checkout_out), 4) if checkout_out else 000
        ep_u = round((money / load_page), 4) if load_page else 000
        zero_per = round((zero_order_thanks / order_thanks), 4) if order_thanks else 000
        u2_p = round(((order_thanks - zero_order_thanks) / load_page), 4) if load_page else 000

        # 日期
        one_result['datetime'] = str(one_day_data['load-page'][0]['tm']) if len(one_day_data['load-page']) > 0 else ''
        # 收入：当天订单产生的总金额
        one_result['money'] = money
        # ARPPU：收入/(购买-'0元购买')
        one_result['money_per'] = money_per
        # 购买：成功订单数（订单状态 = processing / shipped / refund）
        one_result['order_thanks'] = order_thanks
        # Order Number：生成的订单数
        one_result['checkout_create'] = checkout_create
        # 加车次数：唯一身份加车次数
        one_result['addto_cart'] = addto_cart
        # 结账次数：唯一身份进入checkout页面的次数 还需修改
        one_result['checkout_out'] = checkout_out
        # 新用户数
        one_result['new_num'] = new_user
        # 用户数    ：唯一身份的user
        one_result['load_page'] = load_page
        # U2Cart：加车次数 / 用户数
        one_result['add_per'] = to_format_str(add_per)
        # Cart2C：结账次数 / 加车次数
        one_result['cart2_c'] = to_format_str(cart2_c)
        # U2Checkout：结账次数 / 用户数
        one_result['u2_checkout'] = to_format_str(u2_checkout)
        # U2O：('Order Number'-'0元order number')/用户数
        one_result['u2_o'] = to_format_str(u2_o)
        # C2O：('Order Number'-'0元order number')/结账次数
        one_result['c2_o'] = to_format_str(c2_o)
        # o2p：(购买-'0元购买')/('Order Number'-'0元order number')
        one_result['o2_p'] = to_format_str(o2_p)
        # C2P：(购买-'0元购买')/结账次数
        one_result['c2_p'] = to_format_str(c2_p)

        # 收入/用户数
        one_result['ep_u'] = to_format_str(ep_u)
        # 成功订单并且金额=0的订单数
        one_result['zero_order_thanks'] = zero_order_thanks
        # 金额=0的订单数
        one_result['zero_checkout_create'] = zero_checkout_create
        # 0元单占比 '0元购买'/购买
        one_result['zero_per'] = to_format_str(zero_per)
        # (购买-'0元购买')/用户数
        one_result['u2_p'] = to_format_str(u2_p)
        # 国家
        one_result['country'] = country_selector(country)

        result.append(one_result)
    return result


def sql_link_country(sql, country, site, tp):
    # 基础的User-Agent过滤条件，排除各种爬虫和机器人
    base_ua_filter = "AND ua NOT REGEXP 'bot|crawl|spider|slurp|robot|curl|facebook|twitter|linkedin|google|baidu|bing|yandex|duckduckgo|Headless|meta-externalagent/1.1|meta-externalfetcher/1.1'"

    # 根据tp参数确定使用的字段
    field = "url" if tp == "addto-cart" else "page"

    # 定义非英语国家代码列表
    non_en_countries = ["fr", "es", "ja", "zh", "ar", "de", "pt"]

    # 根据country参数构建查询条件
    if country == "/all":
        sql_condition = base_ua_filter
    elif country == "/en":
        # 构建排除所有非英语国家代码的条件
        country_filters = [f"AND {field} NOT LIKE '%%/{code}%%'" for code in non_en_countries]
        sql_condition = "\n".join(country_filters + [base_ua_filter])
    else:
        # 构建特定国家代码的条件
        sql_condition = f"AND {field} LIKE '%%{country}%%' {base_ua_filter}"

    return add_condition_to_sql(sql, site, sql_condition)


def add_condition_to_sql(sql, host_var, new_condition):
    """
    在SQL查询的AND `host` = {host_var}条件后添加新条件

    参数:
    sql (str): 原始SQL查询
    host_var (str): 主机名变量（如'gentlebands.com'）
    new_condition (str): 要添加的新条件，如"url LIKE '%example%'"

    返回:
    str: 修改后的SQL查询
    """
    # 构建目标条件的正则模式（支持引号转义）
    target_pattern = f"AND `host` = '{host_var}'"

    # 查找目标条件的位置
    pos = sql.find(target_pattern)
    if pos == -1:
        raise ValueError(f"未找到目标条件: AND `host` = '{host_var}'")

    # 计算插入位置（目标条件之后）
    insert_pos = pos + len(target_pattern)

    # 检查目标条件后是否已有AND
    remaining_sql = sql[insert_pos:].lstrip()
    if remaining_sql.startswith("AND"):
        # 如果已有AND，直接添加新条件（用空格分隔）
        return f"{sql[:insert_pos]} {new_condition}{sql[insert_pos:]}"
    else:
        # 如果没有AND，添加AND和新条件
        return f"{sql[:insert_pos]} AND {new_condition}{sql[insert_pos:]}"


def to_format_str(params_int):
    return format(params_int, '.2%')


def country_selector(value: str) -> str:
    """
    根据传入的 value（如 '/en'）返回对应的描述或处理逻辑，实现一对一枚举选择
    """
    # 以 value 为 key，label 或处理逻辑为 value，构建映射字典
    country_map = {
        '/all': '全量',
        '/en': '英文',
        '/fr': '法语',
        '/es': '西班牙语',
        '/ja': '日语',
        '/zh': '繁体中文',
        '/ar': '阿拉伯语',
        '/de': '德语',
        '/pt': '葡萄牙语',
    }
    # 返回匹配结果，若不存在则返回默认值（可自定义）
    return country_map.get(value, '未知语言')


def get_new_data(start: datetime, end: datetime, num_days: int, site: str):
    db_conn_new = get_mirror_conn()
    sql = f'''
        {select_new_data.replace('star_time', str(start))
        .replace('end_time', str(end))
        .replace('site_str', site)
    };
        '''
    print(sql)
    r = db_conn_new.query_all(sql)
    print(r)
    return r


@require_http_methods(["GET"])
def site_statistics_days(request):
    global counts, select, limit, froms
    params = request.GET
    re = json.loads(json.dumps(def_response_o, ensure_ascii=False))
    start = params['start'] if 'start' in params.keys() else ''
    end = params['end'] if 'end' in params.keys() else ''
    site = params['site'] if 'site' in params.keys() else ''
    country = params['country'] if 'country' in params.keys() else ''
    if not start or not end or not site or not country:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "请指定起止日期,站点和国家"
        return JsonResponse(re)
    start_day = start.split(' ')[0]
    end_day = end.split(' ')[0]
    start = datetime.strptime(start_day, "%Y-%m-%d")
    end = datetime.strptime(end_day, "%Y-%m-%d")
    delta = end - start
    num_days = delta.days
    # if num_days > 15:
    #     re['code'] = 404
    #     re['type'] = 'fail'
    #     re['message'] = "时间间隔不能大于15天"
    #     return JsonResponse(re)
    result = get_data(start, end, num_days + 1, site, country)
    print(result)
    re['result'] = result
    return JsonResponse(re)


@require_http_methods(["GET"])
def down_site_statistics_excel_days(request):
    global counts, select, limit, froms
    params = request.GET
    re = json.loads(json.dumps(def_response_o, ensure_ascii=False))
    start = params['start'] if 'start' in params.keys() else ''
    end = params['end'] if 'end' in params.keys() else ''
    site = params['site'] if 'site' in params.keys() else ''
    country = params['country'] if 'country' in params.keys() else ''
    if not start or not end or not site or not site or not country:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "请指定起止日期,站点和国家"
        return JsonResponse(re)
    start_day = start.split(' ')[0]
    end_day = end.split(' ')[0]
    start = datetime.strptime(start_day, "%Y-%m-%d")
    end = datetime.strptime(end_day, "%Y-%m-%d")
    delta = end - start
    num_days = delta.days
    if num_days>15:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "时间间隔不能大于15天"
        return JsonResponse(re)
    result = get_data(start, end, num_days + 1, site, country)

    start = str(start).split(" ")[0]
    end = str(end).split(" ")[0]
    title_name = f'{start}-to-{end}'
    # 创建一个Excel工作簿
    excel_file = Workbook()
    sheet = excel_file.active
    sheet.title = title_name

    sheet[f'A1'] = "日期"
    sheet[f'B1'] = "收入"
    sheet[f'C1'] = "ARPPU"
    sheet[f'D1'] = "购买"
    sheet[f'E1'] = "Order Number"
    sheet[f'F1'] = "加车次数"
    sheet[f'G1'] = "结账次数"
    sheet[f'H1'] = "新用户数"
    sheet[f'I1'] = "用户数"
    sheet[f'J1'] = "U2Cart"
    sheet[f'K1'] = "Cart2C"
    sheet[f'L1'] = "U2Checkout"
    sheet[f'M1'] = "U2O"
    sheet[f'N1'] = "C2O"
    sheet[f'O1'] = "o2p"
    sheet[f'P1'] = "C2P"
    sheet[f'Q1'] = "EPU"
    sheet[f'R1'] = "0元购买"
    sheet[f'S1'] = "0元order number"
    sheet[f'T1'] = "0元单占比"
    sheet[f'U1'] = "U2P"
    sheet[f'V1'] = "国家"

    for i in range(len(result)):
        no = i + 1

        # 添加一些数据
        sheet[f'A{no + 1}'] = result[i]['datetime']
        sheet[f'B{no + 1}'] = result[i]['money']
        sheet[f'C{no + 1}'] = result[i]['money_per']
        sheet[f'D{no + 1}'] = result[i]['order_thanks']
        sheet[f'E{no + 1}'] = result[i]['checkout_create']
        sheet[f'F{no + 1}'] = result[i]['addto_cart']
        sheet[f'G{no + 1}'] = result[i]['checkout_out']
        sheet[f'H{no + 1}'] = result[i]['new_num']
        sheet[f'I{no + 1}'] = result[i]['load_page']
        sheet[f'J{no + 1}'] = result[i]['add_per']
        sheet[f'K{no + 1}'] = result[i]['cart2_c']
        sheet[f'L{no + 1}'] = result[i]['u2_checkout']
        sheet[f'M{no + 1}'] = result[i]['u2_o']
        sheet[f'N{no + 1}'] = result[i]['c2_o']
        sheet[f'O{no + 1}'] = result[i]['o2_p']
        sheet[f'P{no + 1}'] = result[i]['c2_p']
        sheet[f'Q{no + 1}'] = result[i]['ep_u']
        sheet[f'R{no + 1}'] = result[i]['zero_order_thanks']
        sheet[f'S{no + 1}'] = result[i]['zero_checkout_create']
        sheet[f'T{no + 1}'] = result[i]['zero_per']
        sheet[f'U{no + 1}'] = result[i]['u2_p']
        sheet[f'V{no + 1}'] = result[i]['country']

    # 保存工作簿到内存中的BytesIO
    excel_bytes_io = BytesIO()
    excel_file.save(excel_bytes_io)
    excel_bytes_io.seek(0)

    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = f'attachment; filename={title_name}'
    response.write(excel_bytes_io.getvalue())
    return response